package com.google.code.nexttraffic.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.google.code.nexttraffic.dao.TerminalDao;
import com.google.code.nexttraffic.dao.support.SimpleJdbcDaoSupport;
import com.google.code.nexttraffic.web.model.Terminal;
import com.google.code.nexttraffic.web.model.Terminal_Temp;

public class TerminalDaoJdbcImpl implements TerminalDao {

	private static SimpleJdbcDaoSupport dbConn;
	private static TerminalDaoJdbcImpl daoJdbcImpl;

	private TerminalDaoJdbcImpl() {
	};

	public static TerminalDaoJdbcImpl getInstance() {

		if (daoJdbcImpl == null) {
			dbConn = SimpleJdbcDaoSupport.getInstance();
			daoJdbcImpl = new TerminalDaoJdbcImpl();
		}

		return daoJdbcImpl;
	}

	@Override
	public int insertTerminal(Terminal terminal) {

		Connection conn = dbConn.getCon();

		try {
			Statement stmt = conn.createStatement();
			String query = "insert into terminal values(sq_terminal.nextval,'"
					+ terminal.getTerminalName() + "','"
					+ terminal.getAddress() + "','" + terminal.getTel() + "','"
					+ terminal.getScale() + "','" + terminal.getComments()
					+ "')";
			stmt.executeUpdate(query);

			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 0;
	}

	@Override
	public List<Terminal> getListTerminal() {

		Connection conn = dbConn.getCon();

		List<Terminal> terminalList = null;
		try {
			Statement stmt = conn.createStatement();
			String query = "SELECT * From terminal";
			ResultSet rs = stmt.executeQuery(query);

			terminalList = new ArrayList<Terminal>();

			while (rs.next()) {

				Terminal t = new Terminal(rs.getString(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getString(6));

				terminalList.add(t);
			}

			rs.close();
			stmt.close();

			rs = null;
			stmt = null;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return terminalList;
	}

	@Override
	public Terminal getTerminal(Integer terminalId) {

		Connection conn = dbConn.getCon();

		Terminal t = null;
		try {
			Statement stmt = conn.createStatement();
			String query = "SELECT * From terminal where terminalid="
					+ terminalId;
			ResultSet rs = stmt.executeQuery(query);

			if (rs.next()) {

				t = new Terminal(rs.getString(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getString(6));

			}

			rs.close();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return t;
	}
	
	public Terminal getTerminalDetailInfo(String terminalName) {

		Connection conn = dbConn.getCon();

		Terminal ter = null;
		try {
			Statement stmt = conn.createStatement();
			String query = "SELECT * From terminal where terminalname='"
					+ terminalName + "'";
			ResultSet rs = stmt.executeQuery(query);

			if (rs.next()) {

				ter = new Terminal(rs.getString(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getString(6));

			}

			rs.close();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ter;
	}

	public String getTimeTable(String terminalNumber) {

		Connection conn = dbConn.getCon();

		String ter = null;
		try {
			Statement stmt = conn.createStatement();
			String query = "SELECT STARTTIME From TIMETABLE where TIMETABLEID="
					+ terminalNumber;
			ResultSet rs = stmt.executeQuery(query);

			if (rs.next()) {

				ter =  rs.getString(1);

			}

			rs.close();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ter;
	}

	@Override
	public List<Terminal_Temp> search(Terminal_Temp temp) {

		Connection conn = dbConn.getCon();

		List<Terminal_Temp> terminalTempList = null;
		try {
			Statement stmt = conn.createStatement();

			String query = "SELECT startterminal," + " endterminal,"
					+ " link_terminal," + " link_timetable," + " divsion,"
					+ " distance," + " total_time " + "FROM terminal_temp "
					+ "WHERE startterminal=" + temp.getStartTerminal()
					+ " AND endterminal=" + temp.getEndTerminal()
					+ " AND divsion='" + temp.getDivsion() + "'";

			ResultSet rs = stmt.executeQuery(query);

			terminalTempList = new ArrayList<Terminal_Temp>();

			while (rs.next()) {

				Terminal_Temp tt = new Terminal_Temp();
				tt.setLinkTerminal(rs.getString("LINK_TERMINAL"));
				tt.setLinkTimetable(rs.getString("LINK_TIMETABLE"));
				tt.setTotalTime(rs.getString("TOTAL_TIME"));

				tt.setStartTerminal(rs.getInt("STARTTERMINAL"));
				tt.setEndTerminal(rs.getInt("ENDTERMINAL"));

				tt.setDivsion(rs.getString("DIVSION"));
				tt.setDistance(rs.getString("DISTANCE"));

				terminalTempList.add(tt);
			}

			rs.close();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return terminalTempList;
	}

}
